package com.tecode.myBooks.product.dao;

import com.tecode.myBooks.product.bean.Code;
import com.tecode.myBooks.product.bean.Product;
import com.tecode.myBooks.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2018/11/28.
 */
public class ProductDao {

    
    /**
     * @Author Gabriel
     * @Description //TODO
     * @Date 2018/12/10
     * @Param []
     * @Return java.util.List<com.tecode.myBooks.product.bean.Product>
     */
    public Product getProductById(String productId ,Integer state){
        Connection conn= null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        Product product = null;

        try {

                conn = DBUtil.getConnection();
                String sql ="select productId,productName,Price,numbers,discount,author,productImgUrl,press,pressDate,code.id as codeId,code.codeName as codeName" +
                        " from product left join code on  product.state = code.id" +
                        " where productId =? ";
                if(state != null){
                    sql +=" and state =?";
                }


                ps = conn.prepareStatement(sql);

                ps.setString(1,productId);
            if(state != null){
                ps.setInt(2,state);
            }
                rs = ps.executeQuery();

                if(rs.next()){
                    product = new Product();
                    product.setId(rs.getInt(1));
                    product.setProductName(rs.getString(2));
                    product.setPrice(rs.getDouble(3));
                    product.setNumbers(rs.getInt(4));
                    product.setDiscount(rs.getDouble(5));

                    product.setAuthor(rs.getString(6));
                    product.setProduceImgUrl(rs.getString(7));
                    product.setPress(rs.getString(8));
                    product.setPressDate(rs.getDate(9));

                    Code code = new Code(rs.getInt("codeId"),rs.getString("codeName"));

                    product.setStateCode(code);
                }

        }catch (Exception e){
        e.printStackTrace();
        }finally {
                DBUtil.close(conn,ps,rs);
        }

        return  product;
    }
    /**
     *  查询所有的商品的方法，返回一个商品对象的集合。
     */

    public List<Product> getBookList(Integer state){
        Connection conn= null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        List<Product>  products= new ArrayList<Product>();
        try {
            conn = DBUtil.getConnection();

            String sql ="select productId,productName,price,numbers,author,ProductImgUrl " +
                    " from product ";
            if(state != null){
                sql +="where state =?";
            }

            ps = conn.prepareStatement(sql);
            if(state != null) {
                ps.setInt(1, state);
            }
            rs = ps.executeQuery();
            Product product = null;
            while(rs.next()){
                product = new Product();
                product.setId(rs.getInt("productId"));
                product.setProductName(rs.getString("productName"));
                product.setPrice(rs.getDouble("price"));
                product.setNumbers(rs.getInt("numbers"));
                product.setAuthor(rs.getString("author"));
                product.setProduceImgUrl(rs.getString("ProductImgUrl"));

                products.add(product);

            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
        return  products;
    }

    public List<Product> getBookPage(long statIndex,long pageSize,Integer state){
        Connection conn= null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        List<Product>  products= new ArrayList<Product>();
        try {
            conn = DBUtil.getConnection();

            String sql ="select productId,productName,price,numbers,author,ProductImgUrl,code.id as codeId,code.codeName as codeName from product left join code on  product.state = code.id";

            if(state != null){
                sql +=" where state =? ";
            }

            sql +=" order by productId limit ?,?";

            ps = conn.prepareStatement(sql);
            int index =1;
            if(state != null) {
                ps.setInt(index++, state);
            }
            ps.setLong(index++,statIndex);
            ps.setLong(index++,pageSize);
            rs = ps.executeQuery();
            Product product = null;
            while(rs.next()){
                product = new Product();
                product.setId(rs.getInt("productId"));
                product.setProductName(rs.getString("productName"));
                product.setPrice(rs.getDouble("price"));
                product.setNumbers(rs.getInt("numbers"));
                product.setAuthor(rs.getString("author"));
                product.setProduceImgUrl(rs.getString("ProductImgUrl"));
                product.setStateCode(new Code(rs.getInt("codeId"),rs.getString("codeName")));
                products.add(product);

            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
        return  products;
    }

    public List<Code> getCodeStateList(int codeid) {
            List<Code> codeStateList = new ArrayList<>();
        Connection conn= null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        try{

            conn = DBUtil.getConnection();

            String sql ="select id ,codename from code where codeid =?";


            ps = conn.prepareStatement(sql);

            ps.setInt(1,codeid);

            rs = ps.executeQuery();
            while (rs.next()){
                codeStateList.add(new Code(rs.getInt("id"),rs.getString("codename")));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }



        return  codeStateList;
    }
}
